﻿using Model;
using System;
using System.Data;

namespace BLL
{
    /// <summary>
    /// 类别明细表
    /// </summary>
    public class Yx_DetailBLL : BaseBLL
    {
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="d"></param>
        /// <returns></returns>
        public int Insert(Yx_Detail d, string operatename)
        {
            d.Detail_Id = GetSequence("YX_DETAIL_ID");
            string sql = "insert into Yx_Detail (detail_id,company_id,n_total,accountno,accounttype,openbank,type,operatorid,operatordate,kemuid,n_month,weeknum,foreign_id) values ({12},{0},{1},'{2}',{3},'{4}',{5},{6},to_date('{7}','yyyy-mm-dd hh24:mi:ss'),'{8}','{9}','{10}',{11})";
            sql = string.Format(sql, d.Company_Id, d.N_Total, d.Accountno, d.Accounttype, d.Openbank, d.Type, d.OperatorId, d.OperatorDate, d.Kemuid, d.N_Month, d.WeekNum, d.Foreign_Id, d.Detail_Id);
            return NoQuery(sql, operatename);
        }

        #region 明细查询

        /// <summary>
        /// 明细查询
        /// </summary>
        /// <param name="TypeStr">类别（1.周资金；2.月资金；3.周长期；4.紧急资金）</param>
        /// <param name="CardNo">卡号</param>
        /// <param name="Company">公司编号</param>
        /// <param name="beginDate">开始时间</param>
        /// <param name="EndDate">结束时间</param>
        /// <returns></returns>
        /// <returns></returns>
        public DataTable Select(string TypeStr, string CardNo, string Company, string beginDate, string EndDate)
        {
            string sql = @"select
       case
         when d.type = 1 then
          to_char('周资金申请')
         when d.type = 2 then
          to_char('月资金申请')
         when d.type = 3 then
          to_char('周长期申请')
         else
          to_char('紧急资金申请')
       end as 所属流程,
       c.cmy_name as 公司名称,
       d.kemuid as 项目名称,
       d.n_total as 金额,
       decode(d.accounttype, 1, '支出', 2, '收入') as 类型,
       d.accountno as 卡号,
       d.openbank as 开户行,
       to_char(d.operatordate, 'yyyy/MM/dd hh24:mi') as 录入时间,

       d.foreign_id 外键编号
  from yx_detail d, sys_company c
 where c.cmy_id = d.company_id
   and d.accounttype = 1";
            if (CardNo.Trim().ToString() != "")
            {
                sql += "and substr(d.accountno,0,19) in (" + CardNo + ") ";
            }
            if (Company.Trim().ToString() != "")
            {
                sql += @" and d.company_id =" + Company;
            }
            if (TypeStr.Trim().ToString() != "" && TypeStr.Trim().ToString() != "-1")
            {
                sql += " and d.type = " + TypeStr.ToString();
            }
            sql += @"
         and d.operatordate between to_date('" + beginDate.Trim() + @" 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and
       to_date('" + EndDate.Trim() + " 23:59:59', 'yyyy-MM-dd hh24:mi:ss')  order by d.detail_id";

            DataTable dt = Query(sql);
            return dt;
        }

        #endregion 明细查询

        /// <summary>
        /// 统计查询
        /// </summary>
        /// <param name="Types">类别（1.周资金；2.月资金；3.周长期；4.紧急资金）</param>
        /// <param name="beginDate">开始时间</param>
        /// <param name="EndDate">结束时间</param>
        /// <param name="CompanyId">公司编号</param>
        /// <param name="WeekNum">第几周 （针对周资金 ，无 传空）</param>
        /// <returns></returns>
        public DataTable selectStatistics(int Types, string beginDate, string EndDate, string CompanyId, string WeekNum)
        {
            string sql = "";

            #region 周资金

            if (Types == 1)
            {
                sql = @"
select
       company_id,
       sum(n_total) as n_total,
       accountno,
       accounttype,
       openbank,
       type,
       operatorid,
       operatordate as 录入时间,
       n_month,
       weeknum,
       foreign_id,
       cmy_name as 公司
  from (select detail_id,
               company_id,
               n_total,
               accountno,
               accounttype,
               openbank,
               type,
               operatorid,
               operatordate,
               kemuid,
               n_month,
               weeknum,
               foreign_id,
               c.cmy_name
          from qs_week_main m,
               qs_week_son s,
               yx_detail d,
               sys_company c,
               (select f.pro_name from qs_fundplan f where f.fatherid = 0) projectName
         where c.cmy_id = d.company_id
           and m.w_id = s.mian_id
           and d.type = 1
           and d.foreign_id = m.w_id
           and m.w_id in
               (select d.foreign_id from yx_detail d group by d.foreign_id)
           and m.issave = 1
           and d.accounttype=1
           and s.n_fundid in
               (select f.pro_id from qs_fundplan f where f.fatherid = 0)
           and d.kemuid like projectName.Pro_Name ";
                if (beginDate.Trim() != "" && EndDate.Trim() != "")
                {
                    sql += "and d.operatordate between to_date('" + beginDate.Trim() + " 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('" + EndDate.Trim() + " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
                }
                if (CompanyId.Trim().ToString() != "" && CompanyId.Trim().ToString() != "-1")
                {
                    sql += "and d.company_id=" + CompanyId;
                }
                sql += @"
         group by detail_id,
                  company_id,
                  accountno,
                  accounttype,
                  openbank,
                  type,
                  operatorid,
                  operatordate,
                  kemuid,
                  n_month,
                  weeknum,
                  foreign_id,
                  n_total,
                  c.cmy_name)
 group by
          company_id,
          accountno,
          accounttype,
          openbank,
          type,
          operatorid,
          operatordate,
          n_month,
          weeknum,
          foreign_id,
          cmy_name order by operatordate";

                DataTable dt = Query(sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    decimal sum = 0;
                    DataTable dtc = dt.Copy();
                    for (int k = 0; k < dtc.DefaultView.ToTable(true, new string[] { "公司" }).Rows.Count; k++)
                    {
                        int state = 0;
                        int ind = 0;
                        string CompanyName = "";
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (dtc.Rows[k]["公司"].ToString() == dt.Rows[i]["公司"].ToString() && !dt.Rows[i]["公司"].ToString().Contains("小计"))
                            {
                                state = 1;
                                ind = i + 1;
                                sum += Convert.ToDecimal(dt.Rows[i]["n_total"].ToString());
                                CompanyName = dtc.Rows[k]["公司"].ToString();
                            }
                        }
                        if (state == 1)
                        {
                            DataRow dr = dt.NewRow();
                            dt.Rows.InsertAt(dr, ind);
                            dt.Rows[ind]["n_total"] = sum;
                            dt.Rows[ind]["公司"] = CompanyName + "小计";
                        }
                    }
                    DataRow[] drs = dt.Select("公司 like '%小计%'");
                    decimal sums = 0;
                    foreach (DataRow item in drs)
                    {
                        sums += Convert.ToDecimal(item["n_total"].ToString());
                    }
                    DataRow drq = dt.NewRow();
                    int index = dt.Rows.Count;
                    dt.Rows.InsertAt(drq, index);
                    dt.Rows[index]["n_total"] = sums;
                    dt.Rows[index]["公司"] = "总计";
                }

                return dt;
            }

            #endregion 周资金

            #region 月资金

            else if (Types == 2)
            {
                //月资金
                sql = @"
select
       company_id,
       sum(n_total) as n_total,
       accountno,
       accounttype,
       openbank,
       type,
       operatorid,
       operatordate as 录入时间,
       n_month,
       weeknum,
       foreign_id,
       cmy_name as 公司
  from (select detail_id,
               company_id,
               n_total,
               accountno,
               accounttype,
               openbank,
               type,
               d.operatorid,
               operatordate,
               kemuid,
               n_month,
               weeknum,
               foreign_id,
               c.cmy_name
          from qs_monthplan m,
               qs_fundplandetail s,
               yx_detail d,
               sys_company c,
               (select f.pro_name from qs_fundplan f where f.fatherid = 0) projectName
         where c.cmy_id = d.company_id
           and m.fundid = s.mainid
           and d.type = 2
           and d.foreign_id = m.fundid
           and m.fundid in
               (select d.foreign_id from yx_detail d group by d.foreign_id)
           and m.issave = 1
           and d.accounttype=1
           and s.n_fundid in
               (select f.pro_id from qs_fundplan f where f.fatherid = 0)
           and d.kemuid like projectName.Pro_Name ";
                if (beginDate.Trim().ToString() != "" && EndDate.Trim().ToString() != "")
                {
                    sql += " and d.operatordate between to_date('" + beginDate.ToString().Trim() + " 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('" + EndDate.ToString().Trim() + " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
                }
                sql += @"group by detail_id,
                  company_id,
                  accountno,
                  accounttype,
                  openbank,
                  type,
                  d.operatorid,
                  operatordate,
                  kemuid,
                  n_month,
                  weeknum,
                  foreign_id,
                  n_total,
                  c.cmy_name)
 group by
          company_id,
          accountno,
          accounttype,
          openbank,
          type,
          operatorid,
          operatordate,
          n_month,
          weeknum,
          foreign_id,
          cmy_name order by operatordate";
                DataTable dt = Query(sql);

                if (dt != null && dt.Rows.Count > 0)
                {
                    decimal sum = 0;
                    DataTable dtc = dt.Copy();
                    for (int k = 0; k < dtc.DefaultView.ToTable(true, new string[] { "公司" }).Rows.Count; k++)
                    {
                        int state = 0;
                        int ind = 0;
                        string CompanyName = "";
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (dtc.Rows[k]["公司"].ToString() == dt.Rows[i]["公司"].ToString() && !dt.Rows[i]["公司"].ToString().Contains("小计"))
                            {
                                state = 1;
                                ind = i + 1;
                                sum += Convert.ToDecimal(dt.Rows[i]["n_total"].ToString());
                                CompanyName = dtc.Rows[k]["公司"].ToString();
                            }
                        }
                        if (state == 1)
                        {
                            DataRow dr = dt.NewRow();
                            dt.Rows.InsertAt(dr, ind);
                            dt.Rows[ind]["n_total"] = sum;
                            dt.Rows[ind]["公司"] = CompanyName + "小计";
                        }
                    }
                    DataRow[] drs = dt.Select("公司 like '%小计%'");
                    decimal sums = 0;
                    foreach (DataRow item in drs)
                    {
                        sums += Convert.ToDecimal(item["n_total"].ToString());
                    }
                    DataRow drq = dt.NewRow();
                    int index = dt.Rows.Count;
                    dt.Rows.InsertAt(drq, index);
                    dt.Rows[index]["n_total"] = sums;
                    dt.Rows[index]["公司"] = "总计";
                }

                return dt;
            }

            #endregion 月资金

            #region 周长期

            else if (Types == 3)
            {
                sql = @"select company_id,
       sum(n_total) as n_total,
       accountno,
       accounttype,
       openbank,
       operatorid,
       operatordate as 录入时间,
       n_month,
       weeknum,
       foreign_id,
       cmy_name as 公司
  from (select company_id,
               n_total ,
               accountno,
               accounttype,
               openbank,
               d.operatorid,
               operatordate,
               d.n_month,
               d.weeknum,
               foreign_id,
               c.cmy_name
          from qs_applymain   am,
               qs_applydetail ap,
               yx_detail      d,
               sys_company    c
         where c.cmy_id = d.company_id
           and d.type = 3
           and d.accounttype=1
           and ap.applyid = am.applyid
           and am.applyid=d.foreign_id
           and am.applyid in
               (select d.foreign_id from yx_detail d where   d.type=3 group by d.foreign_id )";
                if (beginDate.Trim().ToString() != "" && EndDate.Trim().ToString() != "")
                {
                    sql += "and d.operatordate between to_date('" + beginDate.ToString().Trim() + " 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('" + EndDate.ToString().Trim() + " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
                }
                if (CompanyId.Trim().ToString() != "" && CompanyId.Trim().ToString() != "-1")
                {
                    sql += "and d.company_id=" + CompanyId;
                }
                sql += @"
         group by
                  company_id,
                  accountno,
                  accounttype,
                  openbank,
                  type,
                  n_total,
                  d.operatorid,
                  operatordate,
                  d.n_month,
                  d.weeknum,
                  foreign_id,
                  c.cmy_name
                  )
 group by company_id,
          accountno,
          accounttype,
          openbank,
          operatorid,
          operatordate,
          n_month,
          weeknum,
          foreign_id,
          cmy_name
 order by operatordate";
                DataTable dt = Query(sql);

                if (dt != null && dt.Rows.Count > 0)
                {
                    decimal sum = 0;
                    DataTable dtc = dt.Copy();
                    for (int k = 0; k < dtc.DefaultView.ToTable(true, new string[] { "公司" }).Rows.Count; k++)
                    {
                        int state = 0;
                        int ind = 0;
                        string CompanyName = "";
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (dtc.Rows[k]["公司"].ToString() == dt.Rows[i]["公司"].ToString() && !dt.Rows[i]["公司"].ToString().Contains("小计"))
                            {
                                state = 1;
                                ind = i + 1;
                                sum += Convert.ToDecimal(dt.Rows[i]["n_total"].ToString());
                                CompanyName = dtc.Rows[k]["公司"].ToString();
                            }
                        }
                        if (state == 1)
                        {
                            DataRow dr = dt.NewRow();
                            dt.Rows.InsertAt(dr, ind);
                            dt.Rows[ind]["n_total"] = sum;
                            dt.Rows[ind]["公司"] = CompanyName + "小计";
                        }
                    }
                    DataRow[] drs = dt.Select("公司 like '%小计%'");
                    decimal sums = 0;
                    foreach (DataRow item in drs)
                    {
                        sums += Convert.ToDecimal(item["n_total"].ToString());
                    }
                    DataRow drq = dt.NewRow();
                    int index = dt.Rows.Count;
                    dt.Rows.InsertAt(drq, index);
                    dt.Rows[index]["n_total"] = sums;
                    dt.Rows[index]["公司"] = "总计";
                }

                return dt;
            }

            #endregion 周长期

            #region 紧急资金

            else if (Types == 4)
            {
                sql = @"select sum(d.n_total) as n_total, c.cmy_name as 公司,
d.operatordate as 录入时间
  from yx_detail d, qs_urgentapply u, sys_company c
 where d.type = 4
   and d.accounttype=1
   and d.foreign_id = u.urgentid
   and c.cmy_id = d.company_id";
                if (beginDate.Trim().ToString() != "" && EndDate.Trim().ToString() != "")
                {
                    sql += " and d.operatordate between to_date('" + beginDate.ToString().Trim() + " 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('" + EndDate.ToString().Trim() + " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
                }
                if (CompanyId.Trim().ToString() != "" && CompanyId.Trim().ToString() != "-1")
                {
                    sql += "and d.company_id=" + CompanyId;
                }
                sql += @"
   group by cmy_name,operatordate";
                DataTable dt = Query(sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    decimal sum = 0;
                    DataTable dtc = dt.Copy();
                    for (int k = 0; k < dtc.DefaultView.ToTable(true, new string[] { "公司" }).Rows.Count; k++)
                    {
                        int state = 0;
                        int ind = 0;
                        string CompanyName = "";
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (dtc.Rows[k]["公司"].ToString() == dt.Rows[i]["公司"].ToString() && !dt.Rows[i]["公司"].ToString().Contains("小计"))
                            {
                                state = 1;
                                ind = i + 1;
                                sum += Convert.ToDecimal(dt.Rows[i]["n_total"].ToString());
                                CompanyName = dtc.Rows[k]["公司"].ToString();
                            }
                        }
                        if (state == 1)
                        {
                            DataRow dr = dt.NewRow();
                            dt.Rows.InsertAt(dr, ind);
                            dt.Rows[ind]["n_total"] = sum;
                            dt.Rows[ind]["公司"] = CompanyName + "小计";
                        }
                    }
                    DataRow[] drs = dt.Select("公司 like '%小计%'");
                    decimal sums = 0;
                    foreach (DataRow item in drs)
                    {
                        sums += Convert.ToDecimal(item["n_total"].ToString());
                    }
                    DataRow drq = dt.NewRow();
                    int index = dt.Rows.Count;
                    dt.Rows.InsertAt(drq, index);
                    dt.Rows[index]["n_total"] = sums;
                    dt.Rows[index]["公司"] = "总计";
                }
                return dt;
            }

            #endregion 紧急资金

            return null;
        }
    }
}